#!/usr/bin/python3
# -*- coding:utf-8 -*-
# @Time   :2021/12/13
# @Author :CJX
# @File   :Injection_valve.py

import serial
import time
import binascii
import pymysql


def injection_port(port_number):
    instructions = "0"
    port = "none"
    s = serial.Serial("/dev/ttySC3", 9600)
    a1 = 0
    if port_number == 0:
        instructions = "CC004F0000DDF801"
    elif port_number == 1:
        instructions = "CC00A40106DD5402"  # 切换阀-清洗/保护液口
    elif port_number == 2:
        instructions = "CC00A40201DD5002"  # 缓冲液6
    elif port_number == 3:
        instructions = "CC00A40302DD5202"  # 缓冲液4
    elif port_number == 4:
        instructions = "CC00A40601DD5402"  # 电极水冲洗6
    elif port_number == 5:
        instructions = "11050006FF006EAB"
    elif port_number == 6:
        instructions = "11050006FF006EAB"
    else:
        a1 = 10
        port = "insert_database,error"
    while a1 <= 1:
        hex_str = bytes.fromhex(instructions)
        s.write(hex_str)
        n = s.inWaiting()
        a1 = a1 + 1
        if n:
            str1 = binascii.b2a_hex(s.read(n))
            str_a1 = str1[6:8]
            port = str_a1.decode()
        time.sleep(1)
    s.flushInput()
    return port


def insert_database():
    port = 88
    speed = 88
    s = serial.Serial("/dev/ttySC3", 9600)
    str1 = 0
    a1 = 0
    while a1 <= 1:
        Hex_str = bytes.fromhex("CC003E0000DDE701")
        s.write(Hex_str)
        n = s.inWaiting()
        a1 = a1 + 1
        if n:
            str1 = binascii.b2a_hex(s.read(n))
            str_a1 = str1[12:14]
            port = str_a1.decode()
            str_a2 = str1[6:8]
            speed = str_a2.decode()
            print(port)
            print(speed)
        time.sleep(1)
    s.flushInput()
    # con = pymysql.connect(
    #     host='localhost',
    #     port=3306,
    #     user='root',
    #     password='!qAz2WsX3edc',
    #     db='ph',
    #     charset='utf8'
    # )
    # cur = con.cursor()
    # address = "HCJM001"
    # time1 = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    # cur.executemany(
    #     "INSERT Injection_valve(address,time,injection_port,speed) VALUE(%s,%s,%s,%s)",
    #     [(address, time1, str1, "zz")])
    #
    # cur.execute("SELECT min(id) FROM Injection_valve ")
    # min_mode = int(cur.fetchone()[0])
    # cur.execute("SELECT max(id) FROM Injection_valve ")
    # max_mode = int(cur.fetchone()[0])
    # if (max_mode - min_mode) >= 1000:
    #     sql2 = ("delete FROM Injection_valve " + "WHERE id <= %s " % (max_mode - 100) + " and id >= %s" % min_mode)
    #     cur.execute(sql2)
    #
    # con.commit()
    # cur.close()
    port_speed = str(port) + str(speed)
    return port_speed


def insert_error_num(storage_num, step_name):
    con = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        password='!qAz2WsX3edc',
        db='ph',
        charset='utf8'
    )
    cur = con.cursor()

    time1 = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    if storage_num == 0:  # storage_num代表没问题，重置一下计数
        injection_error_val = 0
    else:
        cur.execute("select injection_error_val from injection_error_storage order by id desc limit 1")
        injection_error_val = int(cur.fetchone()[0]) + 1

    cur.executemany(
        "INSERT injection_error_storage(date,injection_error_val,exe_steps, address) VALUE(%s,%s,%s,%s)",
        [(time1, injection_error_val, storage_num, step_name)])

    cur.execute("SELECT min(id) FROM injection_error_storage ")
    min_mode = int(cur.fetchone()[0])
    cur.execute("SELECT max(id) FROM injection_error_storage ")
    max_mode = int(cur.fetchone()[0])
    if (max_mode - min_mode) >= 10000:
        sql2 = ("delete FROM injection_error_storage " + "WHERE id <= %s " % (
                max_mode - 5000) + " and id >= %s" % min_mode)
        cur.execute(sql2)

    con.commit()
    cur.close()


def step_num(step_name):  # 用来查询跳转到第几步
    con = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        password='!qAz2WsX3edc',
        db='ph',
        charset='utf8'
    )
    cur = con.cursor()
    sql2 = ("select exe_steps from injection_error_storage" + " WHERE address = '%s' " % step_name
            + "order by id desc limit 1")
    cur.execute(sql2)
    exe_steps = int(cur.fetchone()[0])

    return exe_steps


def error_val(step_name):  # 用来查询是否跳转，0为不跳转
    con = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        password='!qAz2WsX3edc',
        db='ph',
        charset='utf8'
    )
    cur = con.cursor()
    sql2 = (
            "select injection_error_val from injection_error_storage" + " WHERE address = '%s' " % step_name
            + "order by id desc limit 1")
    cur.execute(sql2)
    error_val_t = int(cur.fetchone()[0])

    return error_val_t
